<?php
/**
 * +--------------------------------------------------------------
 * | eLuoPHP
 * +--------------------------------------------------------------
 * | Copyright (c) 2016-2018 http://qxwoo.cn All rights reserved.
 * +--------------------------------------------------------------
 * | y139w [y139w@163.com]
 * +--------------------------------------------------------------
 * | SQL语句组装类文件
 * +--------------------------------------------------------------
 */

namespace luo\db;


class Sql
{

    /**
     * 查询语句模板
     * @var string
     */
    protected $select = 'select %FIELD% from %FROM%%AS%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT%';

    /**
     * 添加语句模板
     * @var string
     */
    protected $insert = 'insert into %FROM% (%FIELD%) values (%DATA%)';

    /**
     * 删除语句模板
     * @var string
     */
    protected $delete = 'delete from %FROM% %WHERE%';

    /**
     * 更新语句模板
     * @var string
     */
    protected $update = 'update %FROM% set %SET% %WHERE%';

    /**
     * 获取SQL语句
     * @param $type
     * @param array $options
     * @return string
     */
    public function getSql($type, $options = []){
        $array = [];
        switch($type){
            case 'select':
                $array = [
                    '%FIELD%'  => $this->parseField($options['field']),
                    '%FROM%'   => $this->parseFrom($options['from']),
                    '%WHERE%'  => $this->parseWhere($options['where']),
                    '%GROUP%'  => $this->parseGroup($options['group']),
                    '%HAVING%' => $this->parseHaving($options['having']),
                    '%ORDER%'  => $this->parseOrder($options['order']),
                    '%LIMIT%'  => $this->parseLimit($options['limit']),
                    '%JOIN%'   => $this->parseJoin($options['join']),
                    '%AS%'     => $options['as']
                ];
                break;
            case 'insert':
                $insert = $this->parseData($options['data']);
                $array = [
                    '%FROM%'   => $this->parseFrom($options['from']),
                    '%FIELD%'  => $insert[0],
                    '%DATA%'   => $insert[1],
                ];

                break;
            case 'delete':
                $array = [
                    '%FROM%'   => $this->parseFrom($options['from']),
                    '%WHERE%'  => $this->parseWhere($options['where']),
                ];
                break;
            case 'update':
                $array = [
                    '%FROM%'   => $this->parseFrom($options['from']),
                    '%SET%'    => $this->parseData($options['data'], 'update'),
                    '%WHERE%'  => $this->parseWhere($options['where']),
                ];
                break;
        }

        // 处理成SQL语句
        $parseSql = str_replace(array_keys($array), array_values($array), $this->$type);

        return trim($parseSql);

    }

    /**
     * 解析字段
     * @param $fields
     * @return string
     */
    public function parseField($fields){

        $string = '';
        if($fields == '*' || empty($fields)){
            $string = '*';
        }elseif(is_string($fields)){
            $string = trim($fields);
        }elseif(is_array($fields)){
            $array = [];
            foreach($fields as $key=>$val){
                if(is_numeric($key)){
                    $array[] = trim($val);
                }else{
                    // 解析为别名
                    $array[] = $key . ' as ' . $val;
                }
            }
            $string = implode(',', $array);
        }

        return $string;

    }

    /**
     * 解析表名
     * @param $from
     * @return string
     */
    public function parseFrom($from){

        $string = '';
        if(is_string($from)){
            $string = '`' . trim($from) . '`';
        }elseif(is_array($from)){
            $array = [];
            foreach($from as $key=>$val){
                if(is_numeric($key)){
                    $array[] = '`' . trim($val) . '`';
                }else{
                    $array[] = '`' . $key . '`' . ' as ' . $val;
                }
            }
            $string = implode(',', $array);
        }
        return $string;

    }

    public function parseJoin($join){

        $array = [];
        $string = '';
        if(is_array($join)){
            foreach($join as $val){
                $array[] = ' ' . $val[2] . ' join ' . $val[0] . ' on ' . $val[1];
            }
            $string = implode(' ', $array);
        }
        return $string;

    }

    /**
     * 解析条件
     * [
     *   ['name|pass','like',['%think','php%']],
     *   ['name|pass','like',['%think','php%'],'OR']
     * ]
     * @param $where
     * @return string
     */
    public function parseWhere($where){

        $string = '';

        $func = function($val){
            $array = [];
            if(is_string($val)){
                if(strpos($val, '|')){
                    $array = explode('|', $val);
                }else{
                    $array = $val;
                }
            }
            if(is_array($val) or is_numeric($val)){
                $array = $val;
            }

            return $array;
        };

        $orFunc = function($field, $operation, $condition){

            if(strpos($field, '.')){
                $fieldArray = explode('.', $field);
                $string = ' `' . $fieldArray[0] . '`.`' . $fieldArray[1] . '` ';
            }else{
                $string = ' `' . $field . '` ';
            }

            if(is_array($operation)){
                $string .= ' ' . $operation[0] . ' ';
            }else{
                $string .= ' ' . $operation . ' ';
            }
            if(is_array($condition)){
                if(isset($operation[0]) and in_array($operation[0], ['in', 'between'])){
                    $string .= ' ' . $condition[0] . ' ';
                }else{
                    if (isset($condition[0])):
                        $string .= ' \'' . $condition[0] . '\' ';
                    endif;
                }
            }else{
                if(in_array($operation, ['in', 'between'])){
                    $string .= ' ' . $condition . ' ';
                }else{
                    $string .= ' \'' . $condition . '\' ';
                }
            }
            return $string;
        };

        if(is_string($where)){
            $string = $where;
        }

        if(is_array($where)){
            $args = [];
            foreach($where as $key=>$val){
                if(is_array($val)) {
                    // 字段名
                    $field = $func($val[0]);
                    // 表达式
                    $operation = $func($val[1]);
                    // 条件
                    $condition = $func($val[2]);
                    if (empty($condition) && $condition != 0):
                        $condition = '';
                    endif;

                    if (is_string($field) && is_string($operation)){
                        if(strpos($field, '.')){
                            $fieldArray = explode('.', $field);
                            $field = " `{$fieldArray[0]}`.`{$fieldArray[1]}` ";
                        }else{
                            $field = " `{$field}` ";
                        }
                        if(in_array($operation, ['in', 'between'])){
                            $string = " {$field} {$operation} {$condition} ";
                        }else{
                            $string = " {$field} {$operation} '{$condition}' ";
                        }
                    }else{
                        $arr = [];
                        if (is_array($field)) {
                            foreach ($field as $v) {
                                $arr[] = $orFunc($v, $operation, $condition);
                            }
                            if(count($arr) > 1){
                                $string = '( ' . implode(' or ', $arr) . ' )';
                            }else{
                                $string = implode(' or ', $arr);
                            }
                        }

                    }
                }else{
                    $string = $val;
                }
                if(isset($val[3])){
                    $args[] = [$string, $val[3]];
                }else{
                    $args[] = [$string, 'and'];
                }
            }

            $orString = '';
            $array = [];
            foreach ($args as $key=>$val){

                if(isset($val[1]) && $val[1] == 'or'){

                    $pop = array_pop($array);
                    if($pop):
                        $orString .= '(' . $pop . ' ' . $val[1] . ' ' . $val[0] . ')';
                    else:
                        $orString .= ' ' . $val[0];
                    endif;
                    $array[] = $orString;
                }else{
                    $array[] = $val[0];
                    if(!empty($orString)):
                        $array[] = '( ' . $orString . ' )';
                    endif;
                    $orString = '';
                }

            }
            $string = implode(' and ', $array);
        }

        $string = empty($string) ? '' : ' where ' . $string;
        return $string;

    }

    /**
     * 解析分组
     * @param $group
     * @return string
     */
    public function parseGroup(Array $group){

        $string = '';
        if(!empty($group)){
            $string = ' group by ' . implode(',', $group);
        }

        return $string;

    }

    /**
     * 解析HAVING条件
     * @param $having
     * @return string
     */
    public function parseHaving($having){

        $string = '';
        if(!empty($having)){
            $string = ' having ' . implode('', $having);
        }

        return $string;

    }

    /**
     * 解析排序
     * @param $order
     * @return string
     */
    public function parseOrder($order){

        $string = '';
        if(is_string($order)){
            $string = $order;
        }elseif(is_array($order)){
            $string = implode(' ', $order);
        }

        $string = empty($string) ? '' : ' order by ' . $string;

        return $string;

    }

    /**
     * 解析分页
     * @param $limit
     * @return string
     */
    public function parseLimit($limit){

        $string = '';
        if(is_string($limit)){
            $string = $limit;
        }elseif(is_array($limit)){
            $string = implode(',', $limit);
        }

        $string = empty($string) ? '' : ' limit ' . $string;

        return $string;

    }

    /**
     * 添加记录和更新记录的数据处理
     * @param $data
     * @param string $dml
     * @return array|string
     */
    public function parseData($data, $dml = 'insert'){

        $field = [];
        $value = [];
        foreach($data as $key=>$val){
            if($dml === 'update'){
                $value[] = '`' . $key . '`=\'' . $val . '\'';
            }else{
                $field[] = '`' . $key . '`';
                $value[] = '\'' . $val . '\'';
            }
        }
        return ($dml === 'insert')
            ? [implode(',', $field), implode(',', $value)]
            : implode(',', $value);

    }

}